USE HierarchyIdDemo
GO

-- Reporting an SelfJoin.Org chart with a parent child
-- schema (e.g., a ParentDepartmentID field in
-- the HierarchyId.Org table) would require recursion.  But
-- with the HierarchyID, this can be done with
-- one SELECT statement.
CREATE PROCEDURE HierarchyId.ShowDepartmentChart
	@DepartmentID INT
AS
BEGIN

	DECLARE @TopNode hierarchyid,
		@TopLevel INT

	SELECT @TopNode = Node,
			@TopLevel = Level
		FROM HierarchyId.Org
		WHERE DepartmentID = @DepartmentID

	-- note that parent is descENDent of itself
	SELECT Node.ToString() NodeText, 
			space((Level - @TopLevel) * 5) + DepartmentName Department
		FROM HierarchyId.Org
		WHERE @TopNode.IsDescendant(Node) = 1
		ORDER BY Node

END

GO

EXEC HierarchyId.ShowDepartmentChart 2
EXEC HierarchyId.ShowDepartmentChart 1

GO
